/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2005                    */
/* Created on:     2009/7/8 12:49:01                            */
/*==============================================================*/


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('H_SYS_UserRole') and o.name = 'FK_H_SYS_US_REFERENCE_H_SYS_RO')
alter table H_SYS_UserRole
   drop constraint FK_H_SYS_US_REFERENCE_H_SYS_RO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('h_sys_ControlPoint') and o.name = 'FK_H_SYS_CO_REFERENCE_H_SYS_MO')
alter table h_sys_ControlPoint
   drop constraint FK_H_SYS_CO_REFERENCE_H_SYS_MO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('h_sys_Module') and o.name = 'FK_H_SYS_MO_REFERENCE_H_SYS_SI')
alter table h_sys_Module
   drop constraint FK_H_SYS_MO_REFERENCE_H_SYS_SI
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('h_sys_Plugin') and o.name = 'FK_H_SYS_PL_REFERENCE_H_SYS_SI')
alter table h_sys_Plugin
   drop constraint FK_H_SYS_PL_REFERENCE_H_SYS_SI
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('h_sys_UserDepartment') and o.name = 'FK_H_SYS_US_REFERENCE_H_SYS_DE')
alter table h_sys_UserDepartment
   drop constraint FK_H_SYS_US_REFERENCE_H_SYS_DE
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('h_sys_UserModuleData') and o.name = 'FK_H_SYS_US_REFERENCE_H_SYS_SI')
alter table h_sys_UserModuleData
   drop constraint FK_H_SYS_US_REFERENCE_H_SYS_SI
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('h_sys_UserModuleData') and o.name = 'FK_H_SYS_US_REFERENCE_H_SYS_MO')
alter table h_sys_UserModuleData
   drop constraint FK_H_SYS_US_REFERENCE_H_SYS_MO
go

if exists (select 1
            from  sysobjects
           where  id = object_id('H_SYS_UserRole')
            and   type = 'U')
   drop table H_SYS_UserRole
go

if exists (select 1
            from  sysobjects
           where  id = object_id('h_sys_ControlPoint')
            and   type = 'U')
   drop table h_sys_ControlPoint
go

if exists (select 1
            from  sysobjects
           where  id = object_id('h_sys_Department')
            and   type = 'U')
   drop table h_sys_Department
go

if exists (select 1
            from  sysobjects
           where  id = object_id('h_sys_Log')
            and   type = 'U')
   drop table h_sys_Log
go

if exists (select 1
            from  sysobjects
           where  id = object_id('h_sys_Module')
            and   type = 'U')
   drop table h_sys_Module
go

if exists (select 1
            from  sysobjects
           where  id = object_id('h_sys_Plugin')
            and   type = 'U')
   drop table h_sys_Plugin
go

if exists (select 1
            from  sysobjects
           where  id = object_id('h_sys_Role')
            and   type = 'U')
   drop table h_sys_Role
go

if exists (select 1
            from  sysobjects
           where  id = object_id('h_sys_Site')
            and   type = 'U')
   drop table h_sys_Site
go

if exists (select 1
            from  sysobjects
           where  id = object_id('h_sys_User')
            and   type = 'U')
   drop table h_sys_User
go

if exists (select 1
            from  sysobjects
           where  id = object_id('h_sys_UserDepartment')
            and   type = 'U')
   drop table h_sys_UserDepartment
go

if exists (select 1
            from  sysobjects
           where  id = object_id('h_sys_UserModuleData')
            and   type = 'U')
   drop table h_sys_UserModuleData
go

/*==============================================================*/
/* Table: H_SYS_UserRole                                        */
/*==============================================================*/
create table H_SYS_UserRole (
   UserId               uniqueidentifier     not null,
   RoleId               uniqueidentifier     not null,
   constraint PK_H_SYS_USERROLE primary key (UserId, RoleId)
)
go

/*==============================================================*/
/* Table: h_sys_ControlPoint                                    */
/*==============================================================*/
create table h_sys_ControlPoint (
   ControlPointId       uniqueidentifier     not null,
   ModuleId             uniqueidentifier     not null,
   DisplayName          nvarchar(50)         null,
   Tag                  varchar(10)          null,
   IsManage             bit                  null,
   constraint PK_H_SYS_CONTROLPOINT primary key (ControlPointId, ModuleId)
)
go

/*==============================================================*/
/* Table: h_sys_Department                                      */
/*==============================================================*/
create table h_sys_Department (
   DepartmenId          uniqueidentifier     not null,
   DepartmentName       nvarchar(30)         null,
   ParentId             uniqueidentifier     null,
   Description          nvarchar(500)        null,
   Purview              xml                  null,
   CreateTime           datetime             null,
   constraint PK_H_SYS_DEPARTMENT primary key (DepartmenId)
)
go

/*==============================================================*/
/* Table: h_sys_Log                                             */
/*==============================================================*/
create table h_sys_Log (
   LogId                uniqueidentifier     not null,
   Level                int                  null,
   Exception            text                 null,
   HappenTime           datetime             null,
   constraint PK_H_SYS_LOG primary key (LogId)
)
go

/*==============================================================*/
/* Table: h_sys_Module                                          */
/*==============================================================*/
create table h_sys_Module (
   ModuleId             uniqueidentifier     not null,
   SiteId               uniqueidentifier     null,
   ModuleName           nvarchar(100)        null,
   Folder               nvarchar(100)        null,
   DefaultWebPage       nvarchar(200)        null,
   DefaultManagePage    nvarchar(200)        null,
   IncludeDll           nvarchar(1000)       null,
   Description          nvarchar(500)        null,
   ValidlyKey           nvarchar(1000)       null,
   IsSystem             bit                  null,
   Enabled              bit                  null,
   ModuleOrder          tinyint              null,
   constraint PK_H_SYS_MODULE primary key (ModuleId)
)
go

/*==============================================================*/
/* Table: h_sys_Plugin                                          */
/*==============================================================*/
create table h_sys_Plugin (
   PluginId             uniqueidentifier     not null,
   SiteId               uniqueidentifier     null,
   VirtualPath          tinyint              null,
   Enabled              bit                  null,
   constraint PK_H_SYS_PLUGIN primary key (PluginId)
)
go

/*==============================================================*/
/* Table: h_sys_Role                                            */
/*==============================================================*/
create table h_sys_Role (
   RoleId               uniqueidentifier     not null,
   RoleName             nvarchar(30)         null,
   Purview              xml                  null,
   AddTime              datetime             null,
   IsManage             bit                  null,
   constraint PK_H_SYS_ROLE primary key (RoleId)
)
go

/*==============================================================*/
/* Table: h_sys_Site                                            */
/*==============================================================*/
create table h_sys_Site (
   SiteId               uniqueidentifier     not null,
   SiteName             nvarchar(100)        null,
   Domain               nvarchar(100)        null,
   KeyWord              nvarchar(100)        null,
   Description          nvarchar(100)        null,
   ValidlyKey           nvarchar(1000)       null,
   DefaultLanguage      varchar(8)           null,
   constraint PK_H_SYS_SITE primary key (SiteId)
)
go

/*==============================================================*/
/* Table: h_sys_User                                            */
/*==============================================================*/
create table h_sys_User (
   UserId               uniqueidentifier     not null,
   UserName             nvarchar(30)         null,
   DisplayName          nvarchar(30)         null,
   Password             nvarchar(50)         null,
   PasswordSalt         nvarchar(128)        null,
   Email                nvarchar(100)        null,
   RolesId              uniqueidentifier     null,
   DepartmentId         uniqueidentifier     null,
   PersonalPurview      xml                  null,
   Purview              xml                  null,
   CreateTime           datetime             null,
   DefaultLanguage      varchar(8)           null,
   IsManage             bit                  null,
   constraint PK_H_SYS_USER primary key (UserId)
)
go

/*==============================================================*/
/* Table: h_sys_UserDepartment                                  */
/*==============================================================*/
create table h_sys_UserDepartment (
   UserId               uniqueidentifier     not null,
   DepartmentId         uniqueidentifier     not null,
   constraint PK_H_SYS_USERDEPARTMENT primary key (UserId, DepartmentId)
)
go

/*==============================================================*/
/* Table: h_sys_UserModuleData                                  */
/*==============================================================*/
create table h_sys_UserModuleData (
   ModuleId             uniqueidentifier     not null,
   UserId               uniqueidentifier     not null,
   SiteId               uniqueidentifier     not null,
   Data                 xml                  null,
   constraint PK_H_SYS_USERMODULEDATA primary key (ModuleId, UserId, SiteId)
)
go

alter table H_SYS_UserRole
   add constraint FK_H_SYS_US_REFERENCE_H_SYS_RO foreign key (RoleId)
      references h_sys_Role (RoleId)
go

alter table h_sys_ControlPoint
   add constraint FK_H_SYS_CO_REFERENCE_H_SYS_MO foreign key (ModuleId)
      references h_sys_Module (ModuleId)
go

alter table h_sys_Module
   add constraint FK_H_SYS_MO_REFERENCE_H_SYS_SI foreign key (SiteId)
      references h_sys_Site (SiteId)
go

alter table h_sys_Plugin
   add constraint FK_H_SYS_PL_REFERENCE_H_SYS_SI foreign key (SiteId)
      references h_sys_Site (SiteId)
go

alter table h_sys_UserDepartment
   add constraint FK_H_SYS_US_REFERENCE_H_SYS_DE foreign key (DepartmentId)
      references h_sys_Department (DepartmenId)
go

alter table h_sys_UserModuleData
   add constraint FK_H_SYS_US_REFERENCE_H_SYS_SI foreign key (SiteId)
      references h_sys_Site (SiteId)
go

alter table h_sys_UserModuleData
   add constraint FK_H_SYS_US_REFERENCE_H_SYS_MO foreign key (ModuleId)
      references h_sys_Module (ModuleId)
go

